/**
 * 
 */
package com.lanswon.qzsmk.util;

import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.UUID;

import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.time.DateUtils;
import org.apache.poi.EncryptedDocumentException;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.slf4j.LoggerFactory;

import ch.qos.logback.classic.Logger;

import com.lanswon.qzsmk.base.CardContants;
import com.lanswon.qzsmk.base.HttpContants;
import com.lanswon.qzsmk.base.PathContants;
import com.lanswon.qzsmk.dao.BatchCollectionMapper;
import com.lanswon.qzsmk.dao.NationMapper;
import com.lanswon.qzsmk.exception.ApplicationException;
import com.lanswon.qzsmk.exception.ErrorCode;
import com.lanswon.qzsmk.exception.SystemException;
import com.lanswon.qzsmk.model.Certificate;
import com.lanswon.qzsmk.model.CorpTmp;
import com.lanswon.qzsmk.model.Nation;
import com.lanswon.qzsmk.model.PersonalTmp;
import com.lanswon.qzsmk.service.systemManageService.ParamManageService;
import com.lanswon.qzsmk.vo.Other;
import com.lanswon.qzsmk.vo.ResultObject;
import com.lanswon.qzsmk.vo.ResultStatus;
import com.lanswon.qzsmk.vo.ResultVo;

/**
 * @author sun
 *
 */
public class Excel {

	// 网络端文件夹 192.168.0.12
	// public static final String baseDir = "http://192.168.0.12:9999/qzsmk";

//	public static final String baseDir = "http://127.0.0.1:9999/qzsmk";


	private static Logger logger = (Logger) LoggerFactory.getLogger(Excel.class);

	/**
	 * 获取未导入的批次号
	 */
	public static List<String> unImportApplyNos(String root){
		BatchCollectionMapper bcm = SpringContextHolder.getBean("batchCollectionMapper");
		List<String> query_all_applyNo = bcm.queryBatchApplyNos();
//		List<String> l = HtmlParseUtil.parseHtml(inDir);
		List<String> no_import_applyNo =  new ArrayList<>();
		File f = new File(root+File.separator+PathContants.PROJECT_PATH_EXCEL);
		if(!f.exists()){
			f.mkdirs();
		}
		
		
		File[] ff = f.listFiles();
		if(ff.length == 0){
			return no_import_applyNo;
		}
		for(File fff:ff){
			boolean b = false;
			String filename = fff.getName() ;
			String applyNo = filename.substring(0,filename.indexOf("."));
			for(String s : query_all_applyNo){
				if(applyNo.equals(s)){//已导入
					b = true;
					break;
				}
			}
			
			if(!b){
				if(applyNo.matches("^\\d+.*")){
					no_import_applyNo.add(applyNo);
					logger.debug("未导入批次：【{}】",applyNo);
				}
				
			}
		}
		
		return no_import_applyNo;
	}
	
	public static void main(String[] args) {
		String c = "^\\d+.*";
		System.out.println("123dwf.xlsx".matches(c));		
		
	}

	/**
	 * 读取表内容
	 * 
	 * @param filePath
	 * @return
	 * @throws IOException
	 * @throws InvalidFormatException
	 * @throws EncryptedDocumentException
	 */
	private static Map<Workbook,List<Sheet>> getSheetFromExcel(String filePath) throws EncryptedDocumentException,
			InvalidFormatException, IOException {
		if (!filePath.endsWith(".xls") && !filePath.endsWith(".xlsx")) {
			File v = new File(filePath);
			logger.info("文件[{}]不是excel类型,所在路径：{}", v.getName(), v.getAbsolutePath());
		}
		Map<Workbook,List<Sheet>> map = new HashMap<>();

//		InputStream input = new FileInputStream(new File(filePath));
//		BufferedInputStream bis = new BufferedInputStream(input);
		Workbook workbook = WorkbookFactory.create(new File(filePath));

		// 得到两个工作表
		List<Sheet> ll = new ArrayList<>();
		for (int i = 0; i < 2; i++) {
			ll.add((Sheet) workbook.getSheetAt(i));
		}
		map.put(workbook, ll);
		//流被关闭了。。。
		return map;

	}

	/**
	 * 从表1里读取人员数据
	 * 
	 * @param sheet
	 * @return list:装有PersonalTmp对象的数组
	 * @throws ApplicationException
	 * @throws ParseException
	 */
	private static List<PersonalTmp> readDataFromSheetOne(Sheet sheet, String applyBatchNo) throws ApplicationException {
logger.debug("【{}】正在读取表数据...sheet为空吗【{}】",applyBatchNo,sheet==null);
		// 获得表头
		Row rowHead = ((org.apache.poi.ss.usermodel.Sheet) sheet).getRow(0);

		// 获得数据的总行数
		int totalRowNum = ((org.apache.poi.ss.usermodel.Sheet) sheet).getLastRowNum();
		logger.debug("该表格总行数【{}】",totalRowNum);
		// 要获得属性
		// String applyBatchNo = "";// 申请批次号
		// String fileName = new File(path).getName();
		// applyBatchNo = fileName.substring(0, fileName.indexOf("."));
		String paperType = ""; // 证件类型
		String paperNo = ""; // 证件号码
		String customerName = ""; // 客户姓名
		String gender = ""; // 性别
		String nationality = ""; // 名族
		Date birthday = new Date(); // 出生日期
		String corpName = ""; // 工作单位
		String homePhone = ""; // 家庭电话
		String officePhone = "";// 单位电话
		String mobile = "";// 手机号
		String contact = "";// 通讯地址
		String curPostalcode = "";// 现住址邮编
		// 获得所有数据
		ParamManageService pm = SpringContextHolder.getBean(ParamManageService.class);
		List<PersonalTmp> list = new ArrayList<>();
		NationMapper nm = SpringContextHolder.getBean(NationMapper.class);
		logger.debug("for循环...");
		for (int i = 4; i < totalRowNum+1; i++) {
			PersonalTmp pt = new PersonalTmp();
			pt.setCustomerNo(UUID.randomUUID().toString().replace("-", ""));
			// 获得第i行对象
			pt.setApplyBatchNo(applyBatchNo);
			Row row = ((org.apache.poi.ss.usermodel.Sheet) sheet).getRow(i);
			// 获得获得第i行第2列的 String类型对象
			Cell cell = row.getCell((short) 1);
			String paperTypeName = cell.getStringCellValue().toString();
			
			
			
			try {
				paperType = pm.queryPaperByPaperName(paperTypeName).getCertificateType();
			} catch (ApplicationException e) {
				e.printStackTrace();
				paperType = "00";
			}
			pt.setPaperType(paperType);
//try{
			cell = row.getCell((short) 2);
			paperNo = cell.getStringCellValue().toString();
			
			pt.setPaperNo(paperNo.trim());
			// 客户编号，以后可能需要重新定义，暂且定义证件编号
			// pt.setCustomerNo(paperNo);

			cell = row.getCell((short) 3);
			customerName = cell.getStringCellValue().toString();
			pt.setCustomerName(customerName);
			logger.debug("客户姓名【{}】",customerName);
			cell = row.getCell((short) 4);
			gender = cell.getStringCellValue().toString();
			pt.setGender(gender.equals("男") ? CardContants.GENDER_MALE : CardContants.GENDER_FEMALE);

			cell = row.getCell((short) 5);
			nationality = cell.getStringCellValue().toString();
			pt.setNationality(nm.queryNationByName(nationality).getNumbercode());

			cell = row.getCell((short) 6);

			Date b;
			try {
				b = DateUtils.parseDate(cell.getStringCellValue().toString(), "yyyy/MM/dd");
				pt.setBirthday(b);
			} catch (ParseException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
				 String msg1="文件内容错误：日期转换失败";
				logger.error(msg1);
				throw new SystemException(SystemException.ParseException, msg1);
			}
			

			cell = row.getCell((short) 7);
			corpName = cell.getStringCellValue().toString();
			pt.setCorpName(corpName);

			cell = row.getCell((short) 8);

			homePhone = new DataFormatter().formatCellValue(cell);
			pt.setHomePhone(homePhone.trim());

			cell = row.getCell((short) 9);
			officePhone = new DataFormatter().formatCellValue(cell);
			pt.setOfficePhone(officePhone.trim());

			cell = row.getCell((short) 10);
			mobile = new DataFormatter().formatCellValue(cell);
			pt.setMobile(mobile.trim());

			cell = row.getCell((short) 11);
			contact = cell.getStringCellValue().toString();
			pt.setContact(contact);

			cell = row.getCell((short) 12);
			curPostalcode = new DataFormatter().formatCellValue(cell);
			System.out.println("邮编" + curPostalcode);
			pt.setCurPostalcode(curPostalcode);
//} catch (IllegalArgumentException e) {
//	e.printStackTrace();
//	throw new SystemException(SystemException.IllegalStateException, "请将excel单元格设置文本格式");
//}
			list.add(pt);

		}
		logger.debug("【{}】正在读取表数据完成",applyBatchNo);

		return list;
	}

	/**
	 * 从表2读取单位信息
	 */
	private static CorpTmp readDataFromSheetTwo(Sheet sheet) {

		int totalRowNum = ((org.apache.poi.ss.usermodel.Sheet) sheet).getLastRowNum();

		// for (int i = 2; i <= totalRowNum; i++) {
		CorpTmp c = new CorpTmp();
		Row row = ((org.apache.poi.ss.usermodel.Sheet) sheet).getRow(2);
		Cell cell = row.getCell(1);
		String corpName = cell.getStringCellValue().toString();
		c.setCorpName(corpName);
		row = ((org.apache.poi.ss.usermodel.Sheet) sheet).getRow(3);
		cell = row.getCell(1);
		String corpAbbrName = cell.getStringCellValue().toString();
		c.setCorpAbbrName(corpAbbrName);

		row = ((org.apache.poi.ss.usermodel.Sheet) sheet).getRow(4);
		cell = row.getCell(1);
		String corpType = new DataFormatter().formatCellValue(cell);
		if(StringUtils.isNotEmpty(corpType))
		c.setCorpType(corpType);
		row = ((org.apache.poi.ss.usermodel.Sheet) sheet).getRow(5);
		cell = row.getCell(1);
		String corpLevel = new DataFormatter().formatCellValue(cell);
		if(StringUtils.isNotEmpty(corpLevel))
		c.setCorpLevel(corpLevel);
		row = ((org.apache.poi.ss.usermodel.Sheet) sheet).getRow(6);
		cell = row.getCell(1);
		String corpAddr = cell.getStringCellValue().toString();
		c.setCorpAddr(corpAddr);
		row = ((org.apache.poi.ss.usermodel.Sheet) sheet).getRow(7);
		cell = row.getCell(1);
		String corpPostalcode = new DataFormatter().formatCellValue(cell);
		c.setCorpPostalcode(corpPostalcode);
		row = ((org.apache.poi.ss.usermodel.Sheet) sheet).getRow(8);
		cell = row.getCell(1);
		String linkmanName = cell.getStringCellValue().toString();
		c.setLinkmanName(linkmanName);
		row = ((org.apache.poi.ss.usermodel.Sheet) sheet).getRow(9);
		cell = row.getCell(1);
		String linkmanPhone = new DataFormatter().formatCellValue(cell);
		c.setLinkmanPhone(linkmanPhone);
		row = ((org.apache.poi.ss.usermodel.Sheet) sheet).getRow(10);
		cell = row.getCell(1);
		String linkmanMobile = new DataFormatter().formatCellValue(cell);
		c.setLinkmanMobile(linkmanMobile);
		row = ((org.apache.poi.ss.usermodel.Sheet) sheet).getRow(11);
		cell = row.getCell(1);
		String linkmanFax = new DataFormatter().formatCellValue(cell);
		if(StringUtils.isNotEmpty(linkmanFax))
		c.setLinkmanFax(linkmanFax);
		row = ((org.apache.poi.ss.usermodel.Sheet) sheet).getRow(12);
		cell = row.getCell(1);
		String email = new DataFormatter().formatCellValue(cell);
		c.setEmail(email);
		row = ((org.apache.poi.ss.usermodel.Sheet) sheet).getRow(13);
		cell = row.getCell(1);
		String leader = cell.getStringCellValue().toString();
		c.setLeader(leader);
		row = ((org.apache.poi.ss.usermodel.Sheet) sheet).getRow(14);
		cell = row.getCell(1);
		String leaderPhone = new DataFormatter().formatCellValue(cell);
		c.setLeaderPhone(leaderPhone);
		row = ((org.apache.poi.ss.usermodel.Sheet) sheet).getRow(15);
		cell = row.getCell(1);
		String jpName = cell.getStringCellValue().toString();
		if(StringUtils.isNotEmpty(jpName))
		c.setJpName(jpName);
		row = ((org.apache.poi.ss.usermodel.Sheet) sheet).getRow(16);
		cell = row.getCell(1);
		String jpPaperType = cell.getStringCellValue().toString();
		if(StringUtils.isNotEmpty(jpPaperType))
		c.setJpPaperType(jpPaperType);
		row = ((org.apache.poi.ss.usermodel.Sheet) sheet).getRow(17);
		cell = row.getCell(1);
		String jpPaperNo = new DataFormatter().formatCellValue(cell);
		if(StringUtils.isNotEmpty(jpPaperNo))
		c.setJpPaperNo(jpPaperNo);
		row = ((org.apache.poi.ss.usermodel.Sheet) sheet).getRow(18);
		cell = row.getCell(1);
		String jpPhone = new DataFormatter().formatCellValue(cell);
		if(StringUtils.isNotEmpty(jpPhone))
		c.setJpPhone(jpPhone);
		row = ((org.apache.poi.ss.usermodel.Sheet) sheet).getRow(19);
		cell = row.getCell(1);
		String orgCode = new DataFormatter().formatCellValue(cell);
		if(StringUtils.isNotEmpty(orgCode))
		c.setOrgCode(orgCode);
		row = ((org.apache.poi.ss.usermodel.Sheet) sheet).getRow(20);
		cell = row.getCell(1);
		String accountBank = cell.getStringCellValue().toString();
		if(StringUtils.isNotEmpty(accountBank))
		c.setAccountBank(accountBank);
		row = ((org.apache.poi.ss.usermodel.Sheet) sheet).getRow(21);
		cell = row.getCell(1);
		String basicAccount = new DataFormatter().formatCellValue(cell);
		if(StringUtils.isNotEmpty(basicAccount))
		c.setBasicAccount(basicAccount);
		row = ((org.apache.poi.ss.usermodel.Sheet) sheet).getRow(22);
		cell = row.getCell(1);
		String registerAmt = new DataFormatter().formatCellValue(cell);
		String a = registerAmt == "" ? "0" : registerAmt;
		c.setRegisterAmt(new BigDecimal(a));
		row = ((org.apache.poi.ss.usermodel.Sheet) sheet).getRow(23);
		cell = row.getCell(1);
		String registerDate = new DataFormatter().formatCellValue(cell);
		if (registerDate != "") {
			try {
				c.setRegisterDate(new SimpleDateFormat("yyyy-MM-dd").parse(registerDate));
			} catch (ParseException e) {
				e.printStackTrace();
				 String msg1="日期转换失败";
					logger.error(msg1);
					throw new SystemException(SystemException.ParseException, msg1);

			}
		}
		row = ((org.apache.poi.ss.usermodel.Sheet) sheet).getRow(24);
		cell = row.getCell(1);
		String taxRegisterNo = new DataFormatter().formatCellValue(cell);
		if(StringUtils.isNotEmpty(taxRegisterNo))
		c.setTaxRegisterNo(taxRegisterNo);
		row = ((org.apache.poi.ss.usermodel.Sheet) sheet).getRow(25);
		cell = row.getCell(1);
		String crNo = cell.getStringCellValue().toString();
		if(StringUtils.isNotEmpty(crNo))
		c.setCrNo(crNo);
		row = ((org.apache.poi.ss.usermodel.Sheet) sheet).getRow(26);
		cell = row.getCell(1);
		String custManager = cell.getStringCellValue().toString();
		if(StringUtils.isNotEmpty(custManager))
		c.setCustManager(custManager);
		row = ((org.apache.poi.ss.usermodel.Sheet) sheet).getRow(27);
		cell = row.getCell(1);
		String industryCode = new DataFormatter().formatCellValue(cell);
		if(StringUtils.isNotEmpty(industryCode))
		c.setIndustryCode(industryCode);
		row = ((org.apache.poi.ss.usermodel.Sheet) sheet).getRow(28);
		cell = row.getCell(1);
		String status = new DataFormatter().formatCellValue(cell);
		if(StringUtils.isNotEmpty(status))
		c.setStatus(status);
		row = ((org.apache.poi.ss.usermodel.Sheet) sheet).getRow(29);
		cell = row.getCell(1);
		String corpNo = new DataFormatter().formatCellValue(cell);
		c.setCorpNo(corpNo);

		// }

		return c;
	}

	/**
	 * 封装数据
	 * 
	 * @param filePath
	 * @return
	 * @throws ParseException
	 * @throws ApplicationException
	 * @throws IOException
	 * @throws InvalidFormatException
	 * @throws EncryptedDocumentException
	 */
	public static Map<CorpTmp, List<PersonalTmp>> getSingleData(String filePath, String applyBatchNo)
			throws ParseException, ApplicationException, EncryptedDocumentException, InvalidFormatException,
			IOException {
		logger.info("getSingleData########start()");
		Map<CorpTmp, List<PersonalTmp>> map = new HashMap<>();
		logger.debug("读取表格的路径【{}】",filePath);
		Map<Workbook,List<Sheet>> mm = getSheetFromExcel(filePath);
		for(Entry<Workbook,List<Sheet>> e:mm.entrySet()){
			Workbook workbook = e.getKey();
			List<Sheet> ll = e.getValue();
		
		
		List<PersonalTmp> list = readDataFromSheetOne(ll.get(0), applyBatchNo);
		CorpTmp c = readDataFromSheetTwo(ll.get(1));
		if (logger.isDebugEnabled()) {
			logger.debug(filePath + "***********" + list.size());
		}
		c.setApplyBatchNo(applyBatchNo);
		map.put(c, list);
		workbook.close();
		}
		
		logger.info("getSingleData########end()");
		return map;

	}

	/**
	 * 循环读取Excl
	 * 
	 * @param paths
	 * @return
	 * @throws ParseException
	 * @throws ApplicationException
	 * @throws IOException
	 * @throws InvalidFormatException
	 * @throws EncryptedDocumentException
	 */
	public static List<Map<CorpTmp, List<PersonalTmp>>> readExcelData(String[] paths, String root)
			throws ParseException, ApplicationException, EncryptedDocumentException, InvalidFormatException,
			IOException {
		logger.info("readExcelData########start()");

		List<Map<CorpTmp, List<PersonalTmp>>> list = new ArrayList<>();
		
		logger.debug("readExcelData批次号【{}】",paths.toString());
		if (paths.length != 0) {

			// 遍历所有的文件，将每个文件的单位信息和所有的申请人列表装入map列表返回
			for (String pp : paths) {

				// 20180129 惠国磊 以下1行 修正
				// String p = inDir+pp+".xlsx";

				String p = root +  PathContants.PROJECT_PATH_EXCEL + File.separator + pp + ".xlsx";

			
				Map<CorpTmp, List<PersonalTmp>> map1 = getSingleData(p, pp);

				list.add(map1);
				// FileUtil.moveTotherFolders(pp, outDir);
			}
		} else {
			throw new ApplicationException(ErrorCode.OBJECT_IS_NULL, "String[] paths length = zero ");
		}

		logger.info("readExcelData########end()");
		return list;

	}

	public static void outData(OutputStream out, InputStream is) {
		BufferedInputStream bis = null;
		BufferedOutputStream bos = null;
		try {
			bis = new BufferedInputStream(is);
			bos = new BufferedOutputStream(out);
			byte[] buff = new byte[2048];
			int bytesRead;
			// Simple read/write loop.
			while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
				bos.write(buff, 0, bytesRead);
			}
		} catch (final IOException e) {
			e.printStackTrace();
			String msg1="IO流异常";
			logger.error(msg1);
			throw new SystemException(SystemException.IOException, msg1);

		} finally {
			try {
			if (bis != null)
					bis.close();
			if (bos != null)
					bos.close();
			} catch (IOException e) {
				e.printStackTrace();
				String msg1="IO流关闭异常";
				logger.error(msg1);
				throw new SystemException(SystemException.IOException, msg1);

			}	
		}
	}
	
	
	//验证字段为空
	public static Other validExcel(InputStream inp) throws ApplicationException{
		
		Other other= new Other();
		Workbook workbook;
		try {
			workbook = WorkbookFactory.create(inp);
		} catch (EncryptedDocumentException | InvalidFormatException | IOException e) {
			e.printStackTrace();
			String msg1="读取excel失败";
			logger.error(msg1);
			throw new SystemException(SystemException.IOException, msg1);
			
		}

		// 得到两个工作表
		Sheet sheet1 = (Sheet) workbook.getSheetAt(0);
		Sheet sheet2 = (Sheet) workbook.getSheetAt(1);
		
		int totalRowNum = ((org.apache.poi.ss.usermodel.Sheet) sheet1).getLastRowNum();

		other.setNum(totalRowNum-3);
		
		ParamManageService pm = SpringContextHolder.getBean(ParamManageService.class);
		ResultVo rv = pm.queryAllCertificates();
		ResultStatus rs = rv.getResultStatus();
		List<Certificate> list = new ArrayList<>();
		if(rs.getCode() == HttpContants.RETURN_SUCCESS){
			ResultObject ro = (ResultObject) rv.getServiceObject();
			 list = (List<Certificate>) ro.getItems();
		}else{
			throw new ApplicationException(ErrorCode.OTHER_EXCEPTION, "查询证件类型失败");
		}
		
		 rv = pm.queryNation();
		 rs = rv.getResultStatus();
		List<Nation> list_Nation = new ArrayList<>();
		if(rs.getCode() == HttpContants.RETURN_SUCCESS){
			ResultObject ro = (ResultObject) rv.getServiceObject();
			list_Nation = (List<Nation>) ro.getItems();
		}else{
			throw new ApplicationException(ErrorCode.OTHER_EXCEPTION, "查询名族失败");
		}
		
		for (int i = 4; i < totalRowNum+1; i++) {
			// 获得第i行对象
			Row row = ((org.apache.poi.ss.usermodel.Sheet) sheet1).getRow(i);
			// 获得获得第i行第2列的 String类型对象
			Cell cell = row.getCell((short) 1);
			String paperTypeName = cell.getStringCellValue().toString();
			if(StringUtils.isEmpty(paperTypeName)){
				throw new ApplicationException(ErrorCode.OTHER_EXCEPTION, "表1数据异常：证件类型不能为空");
			}
			ValidateDataUtil.checkPaperName(paperTypeName, list);
			

			cell = row.getCell((short) 2);
			String paperNo = cell.getStringCellValue().toString();
			if(StringUtils.isEmpty(paperNo)){
				throw new ApplicationException(ErrorCode.OTHER_EXCEPTION, "表1数据异常：证件号码不能为空");
			}
			
			cell = row.getCell((short) 3);
			String customerName = cell.getStringCellValue().toString();
			if(StringUtils.isEmpty(customerName)){
				throw new ApplicationException(ErrorCode.OTHER_EXCEPTION, "表1数据异常：姓名不能为空");
			}
			cell = row.getCell((short) 4);
			String gender = cell.getStringCellValue().toString();
			if(StringUtils.isEmpty(gender)){
				throw new ApplicationException(ErrorCode.OTHER_EXCEPTION, "表1数据异常：性别不能为空");
			}
			if(!"男".equals(gender) && !"女".equals(gender) ){
				throw new ApplicationException(ErrorCode.OTHER_EXCEPTION, "表1数据异常：性别填写错误");
			}
			

			cell = row.getCell((short) 5);
			String nationality = cell.getStringCellValue().toString();
			if(StringUtils.isEmpty(nationality)){
				throw new ApplicationException(ErrorCode.OTHER_EXCEPTION, "表1数据异常：民族不能为空");
			}
			ValidateDataUtil.checkNationName(nationality, list_Nation);

			
			cell = row.getCell((short) 6);

			
			
			String birth =cell.getStringCellValue().toString();
			if(StringUtils.isEmpty(birth)){
				throw new ApplicationException(ErrorCode.OTHER_EXCEPTION, "表1数据异常：出生日期不能为空");
			}
			try {
				 DateUtils.parseDate(birth, "yyyy/MM/dd");
			} catch (ParseException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
				 String msg1="文件内容错误：日期转换失败";
				logger.error(msg1);
				throw new SystemException(SystemException.ParseException, msg1);
			}
			

			cell = row.getCell((short) 7);
			String corpName = cell.getStringCellValue().toString();
			if(StringUtils.isEmpty(corpName)){
				throw new ApplicationException(ErrorCode.OTHER_EXCEPTION, "表1数据异常：工作单位不能为空");
			}

			cell = row.getCell((short) 8);

			String homePhone = new DataFormatter().formatCellValue(cell);
			if(StringUtils.isEmpty(homePhone)){
				throw new ApplicationException(ErrorCode.OTHER_EXCEPTION, "表1数据异常：家庭电话不能为空");
			}

			cell = row.getCell((short) 9);
			String officePhone = new DataFormatter().formatCellValue(cell);
			if(StringUtils.isEmpty(officePhone)){
				throw new ApplicationException(ErrorCode.OTHER_EXCEPTION, "表1数据异常：办公电话不能为空");
			}

			cell = row.getCell((short) 10);
			String mobile = new DataFormatter().formatCellValue(cell);
			if(StringUtils.isEmpty(mobile)){
				throw new ApplicationException(ErrorCode.OTHER_EXCEPTION, "表1数据异常：手机不能为空");
			}

			cell = row.getCell((short) 11);
			String contact = cell.getStringCellValue().toString();
			if(StringUtils.isEmpty(contact)){
				throw new ApplicationException(ErrorCode.OTHER_EXCEPTION, "表1数据异常：地址不能为空");
			}

			cell = row.getCell((short) 12);
			String curPostalcode = new DataFormatter().formatCellValue(cell);
			if(StringUtils.isEmpty(curPostalcode)){
				throw new ApplicationException(ErrorCode.OTHER_EXCEPTION, "表1数据异常：邮编不能为空");
			}

		}
		
		
		
		
		
		Row row = ((org.apache.poi.ss.usermodel.Sheet) sheet2).getRow(2);
		Cell cell = row.getCell(1);
		String corpName = cell.getStringCellValue().toString();
		if(StringUtils.isEmpty(corpName)){
			throw new ApplicationException(ErrorCode.OTHER_EXCEPTION, "表2数据异常：单位名称不能为空");
		}
		ValidateDataUtil.checklength(corpName, ValidateDataUtil.MAX_LENGTH_50);

		other.setCorpName(corpName);
		row = ((org.apache.poi.ss.usermodel.Sheet) sheet2).getRow(3);
		cell = row.getCell(1);
		String corpAbbrName = cell.getStringCellValue().toString();
		if(StringUtils.isEmpty(corpAbbrName)){
			throw new ApplicationException(ErrorCode.OTHER_EXCEPTION, "表2数据异常：单位简称不能为空");
		}
		ValidateDataUtil.checklength(corpName, ValidateDataUtil.MAX_LENGTH_10);

		row = ((org.apache.poi.ss.usermodel.Sheet) sheet2).getRow(4);
		cell = row.getCell(1);
		String corpType = new DataFormatter().formatCellValue(cell);
		ValidateDataUtil.checklength(corpType, ValidateDataUtil.MAX_LENGTH_10);

		
		row = ((org.apache.poi.ss.usermodel.Sheet) sheet2).getRow(5);
		cell = row.getCell(1);
		String corpLevel = new DataFormatter().formatCellValue(cell);
		ValidateDataUtil.checklength(corpLevel, ValidateDataUtil.MAX_LENGTH_10);

		
		row = ((org.apache.poi.ss.usermodel.Sheet) sheet2).getRow(6);
		
		cell = row.getCell(1);
		String corpAddr = cell.getStringCellValue().toString();
		if(StringUtils.isEmpty(corpAddr)){
			throw new ApplicationException(ErrorCode.OTHER_EXCEPTION, "表2数据异常：单位基本信息-地址不能为空");
		}
		ValidateDataUtil.checklength(corpName, ValidateDataUtil.MAX_LENGTH_50);
		row = ((org.apache.poi.ss.usermodel.Sheet) sheet2).getRow(7);
		cell = row.getCell(1);
		String corpPostalcode = new DataFormatter().formatCellValue(cell);
		if(StringUtils.isEmpty(corpPostalcode)){
			throw new ApplicationException(ErrorCode.OTHER_EXCEPTION, "表2数据异常：单位基本信息-邮编不能为空");
		}
		if(!ValidateDataUtil.isZipCode(corpPostalcode)){
			throw new ApplicationException(ErrorCode.OTHER_EXCEPTION, "表2数据异常：单位基本信息-邮编【"+corpPostalcode+"】填写错误");
		}
		
		row = ((org.apache.poi.ss.usermodel.Sheet) sheet2).getRow(8);
		cell = row.getCell(1);
		String linkmanName = cell.getStringCellValue().toString();
		if(StringUtils.isEmpty(linkmanName)){
			throw new ApplicationException(ErrorCode.OTHER_EXCEPTION, "表2数据异常：单位基本信息-联系人不能为空");
		}
		ValidateDataUtil.checklength(linkmanName, ValidateDataUtil.MAX_LENGTH_10);
		row = ((org.apache.poi.ss.usermodel.Sheet) sheet2).getRow(9);
		cell = row.getCell(1);
		String linkmanPhone = new DataFormatter().formatCellValue(cell);
		if(StringUtils.isEmpty(linkmanPhone)){
			throw new ApplicationException(ErrorCode.OTHER_EXCEPTION, "表2数据异常：单位基本信息-联系人电话不能为空");
		}
			ValidateDataUtil.checklength(linkmanPhone, ValidateDataUtil.MAX_LENGTH_50);
		
		row = ((org.apache.poi.ss.usermodel.Sheet) sheet2).getRow(10);
		cell = row.getCell(1);
		String linkmanMobile = new DataFormatter().formatCellValue(cell);
		if(StringUtils.isEmpty(linkmanMobile)){
			throw new ApplicationException(ErrorCode.OTHER_EXCEPTION, "表2数据异常：单位基本信息-手机不能为空");
		}else if(!ValidateDataUtil.isMobile(linkmanMobile)){
			throw new ApplicationException(ErrorCode.OTHER_EXCEPTION, "表2数据异常：单位基本信息-联系人手机格式【"+linkmanMobile+"】错误");
		}
		
		row = ((org.apache.poi.ss.usermodel.Sheet) sheet2).getRow(11);
		cell = row.getCell(1);
		String linkmanFax = new DataFormatter().formatCellValue(cell);
		ValidateDataUtil.checklength(linkmanFax, ValidateDataUtil.MAX_LENGTH_15);

		
		row = ((org.apache.poi.ss.usermodel.Sheet) sheet2).getRow(12);
		cell = row.getCell(1);
		String email = new DataFormatter().formatCellValue(cell);
		if(StringUtils.isEmpty(email)){
			throw new ApplicationException(ErrorCode.OTHER_EXCEPTION, "表2数据异常：单位基本信息-邮箱不能为空");
		}else if(!ValidateDataUtil.isEmail(email)){
			throw new ApplicationException(ErrorCode.OTHER_EXCEPTION, "表2数据异常：单位基本信息-邮箱格式【"+email+"】错误");
		}
		
		row = ((org.apache.poi.ss.usermodel.Sheet) sheet2).getRow(13);
		cell = row.getCell(1);
		String leader = cell.getStringCellValue().toString();
		if(StringUtils.isEmpty(leader)){
			throw new ApplicationException(ErrorCode.OTHER_EXCEPTION, "表2数据异常：单位基本信息-负责人姓名不能为空");
		}
		ValidateDataUtil.checklength(leader, ValidateDataUtil.MAX_LENGTH_10);

		row = ((org.apache.poi.ss.usermodel.Sheet) sheet2).getRow(14);
		cell = row.getCell(1);
		String leaderPhone = new DataFormatter().formatCellValue(cell);
		if(StringUtils.isEmpty(leaderPhone)){
			throw new ApplicationException(ErrorCode.OTHER_EXCEPTION, "表2数据异常：单位基本信息-负责人电话不能为空");
		}
		ValidateDataUtil.checklength(leaderPhone, ValidateDataUtil.MAX_LENGTH_15);

		
		row = ((org.apache.poi.ss.usermodel.Sheet) sheet2).getRow(15);
		cell = row.getCell(1);
		String jpName = cell.getStringCellValue().toString();
		ValidateDataUtil.checklength(jpName, ValidateDataUtil.MAX_LENGTH_10);
		
		row = ((org.apache.poi.ss.usermodel.Sheet) sheet2).getRow(16);
		cell = row.getCell(1);
		String jpPaperType = cell.getStringCellValue().toString();
		if(StringUtils.isNotEmpty(jpPaperType)){
			try {
				ValidateDataUtil.checkPaperName(jpPaperType, list);
			} catch (Exception e) {
				e.printStackTrace();
				throw new ApplicationException(ErrorCode.OTHER_EXCEPTION, "表2数据异常：单位基本信息-法人证件类型填写错误");

			}
			
		}
		
		row = ((org.apache.poi.ss.usermodel.Sheet) sheet2).getRow(17);
		cell = row.getCell(1);
		String jpPaperNo = new DataFormatter().formatCellValue(cell);
		if(jpPaperNo!=""){
			if(!CommonUtil.isIDNumber(jpPaperNo)){
				throw new ApplicationException(ErrorCode.OTHER_EXCEPTION, "表2数据异常：单位基本信息-法人证件号码【"+jpPaperNo+"】错误");
			}
		}
		row = ((org.apache.poi.ss.usermodel.Sheet) sheet2).getRow(18);
		cell = row.getCell(1);
		String jpPhone = new DataFormatter().formatCellValue(cell);
		ValidateDataUtil.checklength(jpPhone, ValidateDataUtil.MAX_LENGTH_15);
		
		
		row = ((org.apache.poi.ss.usermodel.Sheet) sheet2).getRow(29);
		cell = row.getCell(1);
		String corpNo = new DataFormatter().formatCellValue(cell);
		if(StringUtils.isEmpty(corpNo)){
			throw new ApplicationException(ErrorCode.OTHER_EXCEPTION, "表2数据异常：单位编号不能为空");
		}else if(!ValidateDataUtil.isEnglishAndNumName(corpNo)){
			throw new ApplicationException(ErrorCode.OTHER_EXCEPTION, "表2数据异常：单位编号只能由英文字母和阿拉伯数字组成表示");
		}else{
			ValidateDataUtil.checklength(corpNo, ValidateDataUtil.MAX_LENGTH_20);
		}
		
		
		try {
			workbook.close();
		} catch (IOException e) {
			e.printStackTrace();
			throw new ApplicationException(ErrorCode.OTHER_EXCEPTION, "读取表格异常");
		}
		
		return other;
	}

}
